package comits.business.tiers.dao;

import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.context.ManagedSessionContext;

import comits.business.tiers.metiers.HistoriqueVente;
import comits.business.tiers.metiers.Prevision;
import comits.business.tiers.orm.hibernate.HibernateSession;

public class HistoriqueVenteDAOImpl implements HistoriqueVenteDAO {

	public void save(HistoriqueVente histv) {
		// TODO Auto-generated method stub
		Session session = HibernateSession.currentSession();
        Transaction tc = session.beginTransaction() ;
        
        SQLQuery sql =  session.createSQLQuery(PropertiesConfig.Get("InsertHistoV"));
        //SQLQuery sql =  session.createSQLQuery("INSERT INTO historiquevente" +
        //		"(NumSite,NumPdt,Periode,Quantite,Site,Year)VALUES(?,?,?,?,?,?)");
        
       // (NumSite,NumPdt,Periode,Quantite,Site)
        sql.setInteger(0,histv.getNumSite());
        sql.setInteger(1,histv.getNumPdt());
        sql.setString(2,histv.getPeriode());
        sql.setInteger(3,histv.getQuantite());
        sql.setString(4,histv.getSite());
        sql.setInteger(5, histv.getYear());
        
      int result = sql.executeUpdate();               
       if (result==1) System.out.println("histo ligne cree");          
       /*
        int result = sql.executeUpdate();               
        if (result==1) System.out.println("histo ligne cree");          

        //int result = sql.executeUpdate();               
        //if (result==1) System.out.println("histo ligne cree");          

*/
        
        tc.commit();
        ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
        HibernateSession.closeSession();
        
        //session.flush();
        //session.clear();

	}

	public HistoriqueVente getHistovMois(int annee,String mois,int numBtq, int numPdt) {
		//utilisation de la session hibernate
		//exécution de la requète et recupération 
		//  utilisation fichier de requète properties : PropertiesConfig.Get("SelectEncoursBoutique")
		SQLQuery sql2 = HibernateSession.currentSession()
		.createSQLQuery(PropertiesConfig.Get("SelectHistovBoutique"))
		.addEntity(HistoriqueVente.class);
		/*SQLQuery sql2 = HibernateSession.currentSession().createSQLQuery("Select * From historiquevente " +
				"where NumSite=? and NumPdt=? and Site='Boutique' " +
				"and Periode=? and Year=?").addEntity(HistoriqueVente.class);*/
		//paramètre de la requete sql
		
		
		sql2.setInteger(0,numBtq);
		
		sql2.setInteger(1,numPdt);
		sql2.setString(2,mois);
		sql2.setInteger(3, annee);
		
		
		//System.out.println("NumSite = "+numBtq+" - NumPdt = "+numPdt+" - Periode = "+mois);
		HistoriqueVente histov = (HistoriqueVente) sql2.uniqueResult();
		ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
		 HibernateSession.closeSession();
		 HibernateSession.currentSession().flush();
		 HibernateSession.currentSession().clear();
		
		// Affichage des résultats de la requète
		//System.out.println(histov.getQuantite());
		
		return histov;
		
	}

	public List<HistoriqueVente> getListHistovente(int annee) {
		// TODO Auto-generated method stub
		Session session = HibernateSession.currentSession();
        SQLQuery sql;
        List<HistoriqueVente> listHV = null;
        
        if (annee == 0){
        	
        	sql = session.createSQLQuery(PropertiesConfig.Get("SelectHistovAn1"));
        	/*sql = session.createSQLQuery("Select distinct numsite, numpdt From historiquevente " +
        			"where numpdt in (select distinct numpdt from historiquevente)");*/
        	listHV = sql.list();
        	System.out.println(listHV);
        }
		return listHV;
	}
	
	public List<HistoriqueVente> getHistoriqueVente(int numBoutique, int numPdt, String periode, int annee){
			
			SQLQuery sql = HibernateSession.currentSession().
			createSQLQuery(PropertiesConfig.Get("SelectHistoVCrit"))
			.addEntity(HistoriqueVente.class);
		
			/*SQLQuery sql = HibernateSession.currentSession().
			createSQLQuery("Select * From historiquevente where NumSite=? and NumPdt=?" +
					" and Site='Boutique' and Periode=? and Year=?")
			.addEntity(HistoriqueVente.class);*/
			
			sql.setInteger(0, numBoutique);
			sql.setInteger(1, numPdt);
			sql.setString(2, periode);
			sql.setInteger(3, annee);
			
			List<HistoriqueVente> listHV = sql.list();
			ManagedSessionContext.unbind(HibernateSession.getSessionFactory());
			
			return listHV;	
		}	
}
